﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Threading.Tasks;

namespace BM008_OS9_IQXEL
{
    public static class TestDataStorage
    {
        private static string _connectionString = "Data Source=TestResults.db;Version=3;";

        // 初始化数据库（如果不存在则创建）
        static TestDataStorage()
        {
            InitializeDatabase();
        }
        // 在初始化数据库时添加唯一约束
        private static void InitializeDatabase()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                var createTableSql = @"
            CREATE TABLE IF NOT EXISTS TestResults (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                AddressCode TEXT NOT NULL,
                TestType TEXT NOT NULL,
                TestItem TEXT NOT NULL,
                LowFreqValue TEXT,
                MidFreqValue TEXT,
                HighFreqValue TEXT,
                TestValue TEXT,
                Result TEXT,
                UpperLimit TEXT,
                LowerLimit TEXT,
                Unit TEXT,
                CreatedTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                UpdatedTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(AddressCode, TestType, TestItem)
            )";

                using (var command = new SQLiteCommand(createTableSql, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 清理指定地址码的所有测试数据
        /// </summary>
        public static int CleanTestDataByAddress(string addressCode)
        {
            if (string.IsNullOrWhiteSpace(addressCode))
            {
                throw new ArgumentException("地址码不能为空");
            }

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();

                    string deleteSql = "DELETE FROM TestResults WHERE AddressCode = @addressCode";
                    using (var command = new SQLiteCommand(deleteSql, connection))
                    {
                        command.Parameters.AddWithValue("@addressCode", addressCode);
                        return command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"清理测试数据时出错: {ex.Message}");
                return -1;
            }
        }

        #region 异步版本
        /// <summary>
        /// 插入测试数据（自动清理旧数据）- 异步版本
        /// </summary>
        public static Task<bool> InsertTestDataWithCleanAsync(string addressCode, string testType, string testItem,
            string lowFreqValue = null, string midFreqValue = null, string highFreqValue = null,
            string testValue = null, string result = null, string upperLimit = null,
            string lowerLimit = null, string unit = null)
        {
            return InsertTestDataAsync(addressCode, testType, testItem, lowFreqValue, midFreqValue, highFreqValue,
                testValue, result, upperLimit, lowerLimit, unit);
        }

        /// <summary>
        /// 插入测试数据 - 异步版本
        /// </summary>
        public static async Task<bool> InsertTestDataAsync(string addressCode, string testType, string testItem,
            string lowFreqValue = null, string midFreqValue = null, string highFreqValue = null,
            string testValue = null, string result = null, string upperLimit = null,
            string lowerLimit = null, string unit = null)
        {
            bool success = false;
            if (string.IsNullOrWhiteSpace(addressCode))
            {
                await Task.Run(() => InsertLog("地址码不能为空"));
            }

            try
            {
                // 使用Task.Run包装同步操作
                await Task.Run(() =>
                {
                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();

                        string insertSql = @"
                    INSERT OR REPLACE INTO TestResults 
                    (AddressCode, TestType, TestItem, LowFreqValue, MidFreqValue, HighFreqValue, 
                     TestValue, Result, UpperLimit, LowerLimit, Unit)
                    VALUES 
                    (@addressCode, @testType, @testItem, @lowFreqValue, @midFreqValue, @highFreqValue,
                     @testValue, @result, @upperLimit, @lowerLimit, @unit)";

                        using (var command = new SQLiteCommand(insertSql, connection))
                        {
                            command.Parameters.AddWithValue("@addressCode", addressCode);
                            command.Parameters.AddWithValue("@testType", (object)testType ?? DBNull.Value);
                            command.Parameters.AddWithValue("@testItem", (object)testItem ?? DBNull.Value);
                            command.Parameters.AddWithValue("@lowFreqValue", (object)lowFreqValue ?? DBNull.Value);
                            command.Parameters.AddWithValue("@midFreqValue", (object)midFreqValue ?? DBNull.Value);
                            command.Parameters.AddWithValue("@highFreqValue", (object)highFreqValue ?? DBNull.Value);
                            command.Parameters.AddWithValue("@testValue", (object)testValue ?? DBNull.Value);
                            command.Parameters.AddWithValue("@result", (object)result ?? DBNull.Value);
                            command.Parameters.AddWithValue("@upperLimit", (object)upperLimit ?? DBNull.Value);
                            command.Parameters.AddWithValue("@lowerLimit", (object)lowerLimit ?? DBNull.Value);
                            command.Parameters.AddWithValue("@unit", (object)unit ?? DBNull.Value);

                            command.ExecuteNonQuery();
                        }
                    }
                });

                // 异步记录日志
                await Task.Run(() =>
                    InsertLog($"Insert:[{addressCode},{testType},{testItem},{lowFreqValue},{midFreqValue},{highFreqValue},{testValue},{result},{upperLimit},{lowerLimit},{unit}]"));
                success = true;
            }
            catch (Exception ex)
            {
                await Task.Run(() => InsertLog($"插入测试数据时出错: {ex.Message}"));
            }
            finally
            {
                // 异步记录日志
                await Task.Run(() =>InsertLog($"插入数据结果:[{success}]"));
            }
            return success;
        }

        /// <summary>
        /// 插入测试数据（自动清理旧数据）- 异步版本
        /// </summary>
        public static Task<bool> InsertTestDataWithCleanAsyncFH(string addressCode, string testType, string testItem,
            string LowFreqValue = null, string MedFreqValue = null, string HighFreqValue = null,
            string testValue = null, string result = null, string UPLimit = null,
            string LPLimit = null, string unit = null)
        {
            return InsertTestDataAsyncFH(addressCode, testType, testItem, LowFreqValue, MedFreqValue, HighFreqValue,
                testValue, result, UPLimit, LPLimit, unit);
        }

        /// <summary>
        /// 插入测试数据 - 异步版本（支持增量更新）
        /// </summary>
        public static async Task<bool> InsertTestDataAsyncFH(string addressCode, string testType, string testItem,
            string lowFreqValue = null, string midFreqValue = null, string highFreqValue = null,
            string testValue = null, string result = null, string upperLimit = null,
            string lowerLimit = null, string units = null)
        {
            bool success = false;

            if (string.IsNullOrWhiteSpace(addressCode))
            {
                await Task.Run(() => InsertLog("地址码不能为空"));
                return false;
            }

            try
            {
                await Task.Run(() =>
                {
                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();

                        // 首先检查是否存在相同addressCode、testType、testItem的记录
                        string checkSql = @"
                    SELECT LowFreqValue, MidFreqValue, HighFreqValue, TestValue, Result, UpperLimit, LowerLimit, Unit 
                    FROM TestResults 
                    WHERE AddressCode = @addressCode AND TestType = @testType AND TestItem = @testItem";

                        string currentLowFreq = null;
                        string currentMidFreq = null;
                        string currentHighFreq = null;
                        string currentTestValue = null;
                        string currentResult = null;
                        string currentUpperLimit = null;
                        string currentLowerLimit = null;
                        string currentUnit = null;

                        using (var checkCommand = new SQLiteCommand(checkSql, connection))
                        {
                            checkCommand.Parameters.AddWithValue("@addressCode", addressCode);
                            checkCommand.Parameters.AddWithValue("@testType", (object)testType ?? DBNull.Value);
                            checkCommand.Parameters.AddWithValue("@testItem", (object)testItem ?? DBNull.Value);

                            using (var reader = checkCommand.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    // 读取现有值
                                    currentLowFreq = reader.IsDBNull(0) ? null : reader.GetString(0);
                                    currentMidFreq = reader.IsDBNull(1) ? null : reader.GetString(1);
                                    currentHighFreq = reader.IsDBNull(2) ? null : reader.GetString(2);
                                    currentTestValue = reader.IsDBNull(3) ? null : reader.GetString(3);
                                    currentResult = reader.IsDBNull(4) ? null : reader.GetString(4);
                                    currentUpperLimit = reader.IsDBNull(5) ? null : reader.GetString(5);
                                    currentLowerLimit = reader.IsDBNull(6) ? null : reader.GetString(6);
                                    currentUnit = reader.IsDBNull(7) ? null : reader.GetString(7);
                                }
                            }
                        }

                        // 合并值：如果新值为null，则使用原有值；如果新值不为null，则使用新值
                        string finalLowFreq = lowFreqValue ?? currentLowFreq;
                        string finalMidFreq = midFreqValue ?? currentMidFreq;
                        string finalHighFreq = highFreqValue ?? currentHighFreq;
                        string finalTestValue = testValue ?? currentTestValue;
                        string finalResult = result ?? currentResult;
                        string finalUpperLimit = upperLimit ?? currentUpperLimit;
                        string finalLowerLimit = lowerLimit ?? currentLowerLimit;
                        string finalUnit = units ?? currentUnit;

                        // 使用INSERT OR REPLACE（需要确保有唯一约束）
                        string insertSql = @"
                    INSERT OR REPLACE INTO TestResults 
                    (AddressCode, TestType, TestItem, LowFreqValue, MidFreqValue, HighFreqValue, 
                     TestValue, Result, UpperLimit, LowerLimit, Unit)
                    VALUES 
                    (@addressCode, @testType, @testItem, @lowFreqValue, @midFreqValue, @highFreqValue,
                     @testValue, @result, @upperLimit, @lowerLimit, @unit)";

                        using (var command = new SQLiteCommand(insertSql, connection))
                        {
                            command.Parameters.AddWithValue("@addressCode", addressCode);
                            command.Parameters.AddWithValue("@testType", (object)testType ?? DBNull.Value);
                            command.Parameters.AddWithValue("@testItem", (object)testItem ?? DBNull.Value);
                            command.Parameters.AddWithValue("@lowFreqValue", (object)finalLowFreq ?? DBNull.Value);
                            command.Parameters.AddWithValue("@midFreqValue", (object)finalMidFreq ?? DBNull.Value);
                            command.Parameters.AddWithValue("@highFreqValue", (object)finalHighFreq ?? DBNull.Value);
                            command.Parameters.AddWithValue("@testValue", (object)finalTestValue ?? DBNull.Value);
                            command.Parameters.AddWithValue("@result", (object)finalResult ?? DBNull.Value);
                            command.Parameters.AddWithValue("@upperLimit", (object)finalUpperLimit ?? DBNull.Value);
                            command.Parameters.AddWithValue("@lowerLimit", (object)finalLowerLimit ?? DBNull.Value);
                            command.Parameters.AddWithValue("@unit", (object)finalUnit ?? DBNull.Value);

                            command.ExecuteNonQuery();
                        }
                    }
                });

                // 异步记录日志
                await Task.Run(() =>
                    InsertLog($"Insert/Update:[{addressCode},{testType},{testItem},Value:{testValue},Low:{lowFreqValue},Mid:{midFreqValue},High:{highFreqValue},LP:{lowerLimit},UP:{upperLimit},uint:{units}]"));

                success = true;
            }
            catch (Exception ex)
            {
                await Task.Run(() => InsertLog($"插入测试数据时出错: {ex.Message}"));
            }
            finally
            {
                await Task.Run(() => InsertLog($"插入数据结果:[{success}]"));
            }

            return success;
        }
        #endregion


        #region 查询功能
        /// <summary>
        /// 查询测试数据 - 支持多种条件组合查询
        /// </summary>
        public static async Task<List<TestResult>> QueryTestDataAsync(
            string addressCode = null,
            string testType = null,
            string testItem = null,
            int? limit = null,
            string orderBy = "UpdatedTime DESC")
        {
            var results = new List<TestResult>();

            try
            {
                await Task.Run(() =>
                {
                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();

                        // 构建动态查询条件
                        var conditions = new List<string>();
                        var parameters = new Dictionary<string, object>();

                        if (!string.IsNullOrWhiteSpace(addressCode))
                        {
                            conditions.Add("AddressCode = @addressCode");
                            parameters.Add("@addressCode", addressCode);
                        }

                        if (!string.IsNullOrWhiteSpace(testType))
                        {
                            conditions.Add("TestType = @testType");
                            parameters.Add("@testType", testType);
                        }

                        if (!string.IsNullOrWhiteSpace(testItem))
                        {
                            conditions.Add("TestItem = @testItem");
                            parameters.Add("@testItem", testItem);
                        }

                        string whereClause = conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";
                        string limitClause = limit.HasValue ? "LIMIT @limit" : "";

                        if (limit.HasValue)
                        {
                            parameters.Add("@limit", limit.Value);
                        }

                        string querySql = $@"
                    SELECT 
                        Id, AddressCode, TestType, TestItem, 
                        LowFreqValue, MidFreqValue, HighFreqValue,
                        TestValue, Result, UpperLimit, LowerLimit, Unit,
                        CreatedTime, UpdatedTime
                    FROM TestResults 
                    {whereClause}
                    ORDER BY {orderBy}
                    {limitClause}";

                        using (var command = new SQLiteCommand(querySql, connection))
                        {
                            // 添加参数
                            foreach (var param in parameters)
                            {
                                command.Parameters.AddWithValue(param.Key, param.Value);
                            }

                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    var result = new TestResult
                                    {
                                        Id = reader.GetInt32(0),
                                        AddressCode = reader.GetString(1),
                                        TestType = reader.GetString(2),
                                        TestItem = reader.GetString(3),
                                        LowFreqValue = reader.IsDBNull(4) ? null : reader.GetString(4),
                                        MidFreqValue = reader.IsDBNull(5) ? null : reader.GetString(5),
                                        HighFreqValue = reader.IsDBNull(6) ? null : reader.GetString(6),
                                        TestValue = reader.IsDBNull(7) ? null : reader.GetString(7),
                                        Result = reader.IsDBNull(8) ? null : reader.GetString(8),
                                        UpperLimit = reader.IsDBNull(9) ? null : reader.GetString(9),
                                        LowerLimit = reader.IsDBNull(10) ? null : reader.GetString(10),
                                        Unit = reader.IsDBNull(11) ? null : reader.GetString(11),
                                    };

                                    results.Add(result);
                                }
                            }
                        }
                    }
                });

                await Task.Run(() =>
                    InsertLog($"查询数据: AddressCode={addressCode}, TestType={testType}, TestItem={testItem}, 结果数量={results.Count}"));
            }
            catch (Exception ex)
            {
                await Task.Run(() => InsertLog($"查询测试数据时出错: {ex.Message}"));
                throw;
            }

            return results;
        }

        /// <summary>
        /// 获取所有唯一的AddressCode列表
        /// </summary>
        public static async Task<List<string>> GetAllAddressCodesAsync()
        {
            var addressCodes = new List<string>();

            try
            {
                await Task.Run(() =>
                {
                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();

                        string querySql = "SELECT DISTINCT AddressCode FROM TestResults ORDER BY AddressCode";

                        using (var command = new SQLiteCommand(querySql, connection))
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                addressCodes.Add(reader.GetString(0));
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                await Task.Run(() => InsertLog($"获取AddressCode列表时出错: {ex.Message}"));
                throw;
            }

            return addressCodes;
        }

        /// <summary>
        /// 获取所有唯一的TestType列表
        /// </summary>
        public static async Task<List<string>> GetAllTestTypesAsync()
        {
            var testTypes = new List<string>();

            try
            {
                await Task.Run(() =>
                {
                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();

                        string querySql = "SELECT DISTINCT TestType FROM TestResults ORDER BY TestType";

                        using (var command = new SQLiteCommand(querySql, connection))
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                testTypes.Add(reader.GetString(0));
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                await Task.Run(() => InsertLog($"获取TestType列表时出错: {ex.Message}"));
                throw;
            }

            return testTypes;
        }

        /// <summary>
        /// 获取指定AddressCode和TestType下的TestItem列表
        /// </summary>
        public static async Task<List<string>> GetTestItemsAsync(string addressCode, string testType)
        {
            var testItems = new List<string>();

            try
            {
                await Task.Run(() =>
                {
                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();

                        string querySql = @"
                    SELECT DISTINCT TestItem 
                    FROM TestResults 
                    WHERE AddressCode = @addressCode AND TestType = @testType 
                    ORDER BY TestItem";

                        using (var command = new SQLiteCommand(querySql, connection))
                        {
                            command.Parameters.AddWithValue("@addressCode", addressCode);
                            command.Parameters.AddWithValue("@testType", testType);

                            using (var reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    testItems.Add(reader.GetString(0));
                                }
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                await Task.Run(() => InsertLog($"获取TestItem列表时出错: {ex.Message}"));
                throw;
            }

            return testItems;
        }

        /// <summary>
        /// 检查是否存在指定条件的记录
        /// </summary>
        public static async Task<bool> ExistsAsync(string addressCode, string testType = null, string testItem = null)
        {
            bool exists = false;

            try
            {
                await Task.Run(() =>
                {
                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();

                        var conditions = new List<string>();
                        var parameters = new Dictionary<string, object> { { "@addressCode", addressCode } };

                        conditions.Add("AddressCode = @addressCode");

                        if (!string.IsNullOrWhiteSpace(testType))
                        {
                            conditions.Add("TestType = @testType");
                            parameters.Add("@testType", testType);
                        }

                        if (!string.IsNullOrWhiteSpace(testItem))
                        {
                            conditions.Add("TestItem = @testItem");
                            parameters.Add("@testItem", testItem);
                        }

                        string whereClause = string.Join(" AND ", conditions);
                        string querySql = $"SELECT COUNT(1) FROM TestResults WHERE {whereClause}";

                        using (var command = new SQLiteCommand(querySql, connection))
                        {
                            foreach (var param in parameters)
                            {
                                command.Parameters.AddWithValue(param.Key, param.Value);
                            }

                            exists = Convert.ToInt32(command.ExecuteScalar()) > 0;
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                await Task.Run(() => InsertLog($"检查记录存在性时出错: {ex.Message}"));
                throw;
            }

            return exists;
        }
        #endregion

        #region 写入报告

        public static void  WriteTestDataToHtml(bool Result,string Address)
        {
            Console.WriteLine("生成报告");
        }

        #endregion

        /// <summary>
        /// 获取指定地址码的所有测试数据
        /// </summary>
        public static List<TestResult> GetTestResultsByAddress(string addressCode)
        {
            var results = new List<TestResult>();

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();

                    string querySql = "SELECT * FROM TestResults WHERE AddressCode = @addressCode ORDER BY TestTime";

                    using (var command = new SQLiteCommand(querySql, connection))
                    {
                        command.Parameters.AddWithValue("@addressCode", addressCode);

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                results.Add(new TestResult
                                {
                                    Id = reader["Id"] != DBNull.Value ? Convert.ToInt32(reader["Id"]) : 0,
                                    AddressCode = reader["AddressCode"].ToString(),
                                    TestType = reader["TestType"] != DBNull.Value ? reader["TestType"].ToString() : null,
                                    TestItem = reader["TestItem"] != DBNull.Value ? reader["TestItem"].ToString() : null,
                                    LowFreqValue = reader["LowFreqValue"] != DBNull.Value ? reader["LowFreqValue"].ToString() : null,
                                    MidFreqValue = reader["MidFreqValue"] != DBNull.Value ? reader["MidFreqValue"].ToString() : null,
                                    HighFreqValue = reader["HighFreqValue"] != DBNull.Value ? reader["HighFreqValue"].ToString() : null,
                                    TestValue = reader["TestValue"] != DBNull.Value ? reader["TestValue"].ToString() : null,
                                    Result = reader["Result"] != DBNull.Value ? reader["Result"].ToString() : null,
                                    UpperLimit = reader["UpperLimit"] != DBNull.Value ? reader["UpperLimit"].ToString() : null,
                                    LowerLimit = reader["LowerLimit"] != DBNull.Value ? reader["LowerLimit"].ToString() : null,
                                    Unit = reader["Unit"] != DBNull.Value ? reader["Unit"].ToString() : null,
                                    TestTime = Convert.ToDateTime(reader["TestTime"])
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询测试数据时出错: {ex.Message}");
            }

            return results;
        }
        /// <summary>
        /// 生成运行log
        /// </summary>
        /// <param name="Log"></param>
        public static void InsertLog(string Log)
        {
            Console.WriteLine(Log);
            if (!Directory.Exists($"{Directory.GetCurrentDirectory()}\\RunLog"))
            {
                Directory.CreateDirectory($"{Directory.GetCurrentDirectory()}\\RunLog");
            }
            string LogPath = $"{Directory.GetCurrentDirectory()}\\RunLog\\InsertLogL_{DateTime.Now.ToString("yyyyMMdd")}.txt";
            try
            {
                if (File.Exists(LogPath))
                {
                    StreamWriter SW = new StreamWriter(LogPath, true);
                    SW.Write(Log + "\r\n");
                    //await SW.WriteAsync(Log + "\r\n");
                    SW.Close();
                }
                else
                {
                    StreamWriter SW = new StreamWriter(LogPath, true);
                    SW.Write(Log + "\r\n");
                    SW.Close();
                }
            }
            catch (Exception exp)
            {
                Console.WriteLine(exp);
            }
        }
        /// <summary>
        /// 测试结果类
        /// </summary>
        public class TestResult
        {
            public int Id { get; set; }
            public string AddressCode { get; set; }
            public string TestType { get; set; }
            public string TestItem { get; set; }
            public string LowFreqValue { get; set; }  // 改为字符串类型
            public string MidFreqValue { get; set; }   // 改为字符串类型
            public string HighFreqValue { get; set; }  // 改为字符串类型
            public string TestValue { get; set; }
            public string Result { get; set; }
            public string UpperLimit { get; set; }     // 改为字符串类型
            public string LowerLimit { get; set; }     // 改为字符串类型
            public string Unit { get; set; }
            public DateTime TestTime { get; set; }

            public override string ToString()
            {
                string valueStr;
                if (!string.IsNullOrEmpty(TestValue))
                {
                    valueStr = TestValue;
                }
                else if (!string.IsNullOrEmpty(LowFreqValue) ||
                         !string.IsNullOrEmpty(MidFreqValue) ||
                         !string.IsNullOrEmpty(HighFreqValue))
                {
                    valueStr = $"{LowFreqValue ?? "N/A"}/{MidFreqValue ?? "N/A"}/{HighFreqValue ?? "N/A"}";
                }
                else
                {
                    valueStr = "N/A";
                }

                string limitStr = $"{(UpperLimit ?? "N/A")}/{(LowerLimit ?? "N/A")}";
                string resultStr = !string.IsNullOrEmpty(Result) ? $" [{Result}]" : "";

                return $"{TestType?.PadRight(10)}{TestItem?.PadRight(15)}{valueStr.PadLeft(20)}" +
                       $"{limitStr.PadLeft(15)}{Unit?.PadLeft(8)}{resultStr}";
            }
        }
    }
}